
create PROC [dbo].[EPOCheckValidateOrderId]
    @PharmacyId DECIMAL(19, 0),
    @OrderId BIGINT
AS
BEGIN
    DECLARE @StatusMsg NVARCHAR(MAX);
    DECLARE @StatusCode TINYINT = 0;
    DECLARE @LastOrderStatus TINYINT = 0;

    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        /*01 ------------------------------------------ Check Exists Tmp DrugH Prescritpion*/
        SET @StatusCode = 100;
        SET @StatusMsg = N' ǘ    ';
        IF NOT EXISTS (SELECT 1 FROM dbo.TmpDrugH WHERE Id_Havaleh = @PharmacyId)
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;

        /*02 ------------------------------------------ Check Exists OrderId*/
        SET @StatusCode = 101;
        SET @StatusMsg = N'     ';
        IF NOT EXISTS (SELECT 1 FROM dbo.EPOTracking WHERE OrderId = @OrderId)
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;

        /*03 ------------------------------------------ Check Order Status*/
        SELECT @LastOrderStatus = [Status]
        FROM dbo.EPOTracking
        WHERE OrderId = @OrderId;

        SET @StatusCode = 102;
        SET @StatusMsg = N'        ';
        IF (@LastOrderStatus = 1)
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 103;
        SET @StatusMsg = N'        ǘ  ';
        IF (@LastOrderStatus = 9)
            RAISERROR(@StatusMsg, 16, 1);

            
        SET @StatusCode = 110;
        SET @StatusMsg = N'     ǘ          ';
        IF (@LastOrderStatus = 11)
            RAISERROR(@StatusMsg, 16, 1);


        SET @StatusCode = 109;
        SET @StatusMsg = N'             ';
        IF (@LastOrderStatus = 13 )
            RAISERROR(@StatusMsg, 16, 1);


        SET @StatusCode = 104;
        SET @StatusMsg = N'         ';
        IF (@LastOrderStatus = 30)
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 105;
        SET @StatusMsg = N'         ';
        IF (@LastOrderStatus = 40)
            RAISERROR(@StatusMsg, 16, 1);
        


        /*04 ------------------------------------------ Check Order Status*/
        SET @StatusCode = 108;
        SET @StatusMsg = N'            ';
        --DECLARE @PreInvoice DEC(16, 0) = 0;
        IF (EXISTS
        (
            SELECT 1
            FROM dbo.TmpDrugH
            WHERE ISNULL(OrderId, 0) = @OrderId
                  AND Id_Havaleh <> @PharmacyId
        )
           )
            RAISERROR(@StatusMsg, 16, 1);

        /*Final ------------------------------------------ */
        SET @StatusCode = 1;
        SET @StatusMsg = N'        ';
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;
        --RAISERROR(@StatusMsg, 16, 1);
        PRINT ERROR_MESSAGE();
        ROLLBACK TRAN;
    END CATCH;
END;
